import { BaseModel } from './BaseModel';
import { query } from '../../config/database';

export interface IConnection {
  id: string;
  name: string;
  host: string;
  port: number;
  username: string;
  password_encrypted: string;
  database_name?: string;
  ssl_enabled: boolean;
  timeout: number;
  max_connections: number;
  connection_params: Record<string, any>;
  created_at: Date;
  updated_at: Date;
  created_by?: string;
  is_active: boolean;
}

export class Connection extends BaseModel {
  constructor() {
    super('connections');
  }
  
  // 创建连接（自动加密密码）
  async createConnection(connectionData: {
    name: string;
    host: string;
    port: number;
    username: string;
    password: string;
    database_name?: string;
    ssl_enabled?: boolean;
    timeout?: number;
    max_connections?: number;
    connection_params?: Record<string, any>;
    created_by?: string;
  }): Promise<IConnection> {
    const result = await query(
      `INSERT INTO connections (
        name, host, port, username, password_encrypted, database_name,
        ssl_enabled, timeout, max_connections, connection_params, created_by
      )
      VALUES ($1, $2, $3, $4, crypt($5, gen_salt('bf')), $6, $7, $8, $9, $10, $11)
      RETURNING *`,
      [
        connectionData.name,
        connectionData.host,
        connectionData.port,
        connectionData.username,
        connectionData.password,
        connectionData.database_name,
        connectionData.ssl_enabled || false,
        connectionData.timeout || 30000,
        connectionData.max_connections || 10,
        JSON.stringify(connectionData.connection_params || {}),
        connectionData.created_by
      ]
    );
    return result.rows[0];
  }
  
  // 更新连接密码
  async updatePassword(id: string, newPassword: string): Promise<boolean> {
    const result = await query(
      `UPDATE connections 
       SET password_encrypted = crypt($2, gen_salt('bf')), updated_at = CURRENT_TIMESTAMP
       WHERE id = $1`,
      [id, newPassword]
    );
    return result.rowCount > 0;
  }
  
  // 获取活跃连接列表（不包含密码）
  async getActiveConnections(): Promise<Omit<IConnection, 'password_encrypted'>[]> {
    const result = await query(
      `SELECT 
        id, name, host, port, username, database_name, ssl_enabled,
        timeout, max_connections, connection_params, created_at, updated_at,
        created_by, is_active
       FROM connections 
       WHERE is_active = true
       ORDER BY name ASC`
    );
    return result.rows;
  }
  
  // 获取连接详情（不包含密码）
  async getConnectionById(id: string): Promise<Omit<IConnection, 'password_encrypted'> | null> {
    const result = await query(
      `SELECT 
        id, name, host, port, username, database_name, ssl_enabled,
        timeout, max_connections, connection_params, created_at, updated_at,
        created_by, is_active
       FROM connections 
       WHERE id = $1`,
      [id]
    );
    return result.rows[0] || null;
  }
  
  // 获取连接详情（包含加密密码，用于实际连接）
  async getConnectionForAuth(id: string): Promise<IConnection | null> {
    const result = await query(
      'SELECT * FROM connections WHERE id = $1 AND is_active = true',
      [id]
    );
    return result.rows[0] || null;
  }
  
  // 检查连接名称是否唯一
  async isNameUnique(name: string, excludeId?: string): Promise<boolean> {
    let sql = 'SELECT 1 FROM connections WHERE name = $1';
    const params: any[] = [name];
    
    if (excludeId) {
      sql += ' AND id != $2';
      params.push(excludeId);
    }
    
    const result = await query(sql, params);
    return result.rows.length === 0;
  }
  
  // 激活/停用连接
  async toggleActive(id: string, isActive: boolean): Promise<boolean> {
    const result = await query(
      `UPDATE connections 
       SET is_active = $2, updated_at = CURRENT_TIMESTAMP
       WHERE id = $1`,
      [id, isActive]
    );
    return result.rowCount > 0;
  }
  
  // 获取连接使用统计
  async getConnectionStats(id: string): Promise<{
    total_executions: number;
    successful_executions: number;
    failed_executions: number;
    avg_execution_time: number;
    last_used: Date | null;
  }> {
    const result = await query(
      `SELECT 
        COUNT(*) as total_executions,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as successful_executions,
        COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_executions,
        AVG(duration) as avg_execution_time,
        MAX(start_time) as last_used
       FROM executions 
       WHERE connection_id = $1`,
      [id]
    );
    
    const stats = result.rows[0];
    return {
      total_executions: parseInt(stats.total_executions, 10),
      successful_executions: parseInt(stats.successful_executions, 10),
      failed_executions: parseInt(stats.failed_executions, 10),
      avg_execution_time: parseFloat(stats.avg_execution_time) || 0,
      last_used: stats.last_used
    };
  }
  
  // 测试连接配置（不实际连接，只验证参数）
  async validateConnectionConfig(connectionData: {
    host: string;
    port: number;
    username: string;
    database_name?: string;
  }): Promise<{ valid: boolean; errors: string[] }> {
    const errors: string[] = [];
    
    // 验证主机名
    if (!connectionData.host || connectionData.host.trim() === '') {
      errors.push('主机名不能为空');
    }
    
    // 验证端口
    if (!connectionData.port || connectionData.port < 1 || connectionData.port > 65535) {
      errors.push('端口必须在1-65535之间');
    }
    
    // 验证用户名
    if (!connectionData.username || connectionData.username.trim() === '') {
      errors.push('用户名不能为空');
    }
    
    return {
      valid: errors.length === 0,
      errors
    };
  }
}